/* Formatted on 2017-02-05 23:24:44 (QP5 v5.115.810.9015) */
CREATE OR REPLACE FUNCTION SPLIT (p_list CLOB, p_sep varchar2:= ',')
   RETURN tabletype
   PIPELINED
IS
   l_idx    PLS_INTEGER;
   v_list   CLOB := p_list;
BEGIN
   LOOP
      l_idx   := INSTR (v_list, p_sep);

      IF l_idx > 0
      THEN
         PIPE ROW (SUBSTR (v_list, 1, l_idx - 1));
         v_list   := SUBSTR (v_list, l_idx + LENGTH (p_sep));
      ELSE
         PIPE ROW (v_list);
         EXIT;
      END IF;
   END LOOP;
END;
/

CREATE TABLE sys_config (
   config_id NUMBER NOT NULL PRIMARY KEY,
   config_owner VARCHAR2 (30) NOT NULL,
   config_app VARCHAR2 (30) DEFAULT 'sys' NOT NULL,
   config_module VARCHAR2 (30) NOT NULL,
   config_section VARCHAR2 (30),
   config_key VARCHAR2 (30) NOT NULL,
   config_value VARCHAR2 (4000) NOT NULL
);

CREATE UNIQUE INDEX ix_sys_config
   ON sys_config
      (config_owner, config_app, config_module, config_section, config_key);

CREATE SEQUENCE seq_sys_config_id
   START WITH 1
   NOCACHE;

/* Formatted on 2017-02-05 23:24:27 (QP5 v5.115.810.9015) */
CREATE TRIGGER trg_sys_config_id
   BEFORE INSERT
   ON sys_config
   FOR EACH ROW
BEGIN
   SELECT seq_sys_config_id.NEXTVAL
   INTO :new.config_id
   FROM DUAL;
END;
/

INSERT INTO sys_config VALUES (1,'system','sys','common','global','version','3.7');
INSERT INTO sys_config VALUES (2,'system','sys','cron','run','status','running');
INSERT INTO sys_config VALUES (3,'admin','sys','common','','blockInited','1');

CREATE OR REPLACE PACKAGE sys_config_pk
IS
   TYPE curtype IS REF CURSOR;

   PROCEDURE get_user_config_sp (v_account_list IN varchar2, c_result OUT curtype);
END;
/

CREATE OR REPLACE PACKAGE BODY sys_config_pk
IS
   PROCEDURE get_user_config_sp (v_account_list IN varchar2,
                                 c_result OUT curtype
   )
   IS
   BEGIN
      OPEN c_result FOR
         SELECT config_owner AS owner,
                config_app AS app,
                config_module AS module,
                config_section AS section,
                config_key AS key,
                config_value AS VALUE
         FROM sys_config
         WHERE config_owner IN (SELECT *
                                FROM table (split (v_account_list)));
   END;
END;
/
